package edu.ajou.scw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DatabaseConnector {

	String url = "jdbc:mysql://localhost:3306/coworker";
	String id = "root";
	String pwd = "wireless";
	
	Connection conn;
	Statement stmt;
	
	public ResultSet getValue(String table, String field, String where) throws SQLException
	{
		ResultSet rs = null;
		if (field == null)
			field = "*";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, id, pwd);
			Statement stmt = conn.createStatement();
			
			String sql = String.format("select %s from %s", field, table);
			
			if (where != null)
			{
				sql += " where " + where;
			}
			
			rs = stmt.executeQuery(sql);
			
			//Log.d("[Query] " + sql);
			
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public int query(String sql)
	{
		int retVal = -1;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, id, pwd);
			Statement stmt = conn.createStatement();

			retVal = stmt.executeUpdate(sql);
			
			close();
			
			//Log.d("[Query] " + sql);
			
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return retVal;
	}
	
	public void close()
	{
		if (stmt != null)
		{
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null)
		{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<UserInfo> getUserInfo(String _id, boolean exact)
	{
		ArrayList<UserInfo> ui = null;
		
		try {
			String where = "";
			
			if (exact)
			{
				where = "`id` = \"" + _id + "\"";
			}
			else
			{
				where = "`id` like \"%" + _id + "%\"";
			}
			
			ResultSet rs = getValue("usr_info", null, where);
			
			ui = new ArrayList<UserInfo>();
			while (rs.next())
			{
				String id = rs.getString("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				String image = rs.getString("image");
				
				UserInfo user = new UserInfo(id,name,email);
				user.setImage(image);
				ui.add(user);
			}
			
			close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (NullPointerException e)
		{
			e.printStackTrace();
		}
		
		return ui;
	}
	
	public boolean auth(String id, String password)
	{
		try {
			ResultSet rs = getValue("usr_info", null, String.format("`id` = \"%s\" and `password` = \"%s\"", id, password));
			boolean valid = rs.next();
			
			close();
			
			return valid;
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (NullPointerException e)
		{
			e.printStackTrace();
		}
		
		return false;
	}
	
	public String findTeamByLeader(String _leader)
	{
		String team_id = null;
		try {
			ResultSet rs = getValue("team_info", null, "`leader` = \"" + _leader + "\"");
			if (rs.next())
			{
				team_id = rs.getString("team_id");
			}
			
			close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (NullPointerException e)
		{
			e.printStackTrace();
		}
		
		return team_id;
	}
	
	public Team getTeamById(String _id)
	{
		Team t = null;
		
		
		try {
			ResultSet rs = getValue("team_info", null, "`team_id` = \"" + _id + "\"");
			
			if (rs.next())
			{
				String leader = rs.getString("leader");
				String type = rs.getString("type");
				
				t = new Team(_id, leader, type);
				
				t.parseMembers(rs.getString("members"));
				t.parseSlides(rs.getString("slides"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException e)
		{
			e.printStackTrace();
		}

		
		return t;
	}
	
	public String[] getLoginInfo(String id)
	{
		String[] retVal = null;
		
		try {
			ResultSet rs = getValue("login_info", "device, connection, logon", "`id`=\"" + id + "\"");
			if (rs.next())
			{
				retVal = new String[3];
				retVal[0] = rs.getString("device");
				retVal[1] = rs.getString("connection");
				retVal[2] = rs.getString("logon");
				
				if (retVal[0] == null || retVal[1] == null || retVal[2] == null)
					retVal = null;
				close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException e)
		{
			e.printStackTrace();
		}
		
		return retVal;
	}
	
	public ArrayList<UserInfo> getFriendList(String id)
	{
		ArrayList<UserInfo> list = new ArrayList<UserInfo>();
		
		try {
			ResultSet rs = getValue("friend inner join usr_info", "friend.friend_id, usr_info.name, usr_info.email, usr_info.image", "friend.id=\"" + id + "\" and friend.friend_id = usr_info.id");
			
			while (rs.next())
			{
				String friend_id = rs.getString(1);
				String name = rs.getString(2);
				String email = rs.getString(3);
				String image = rs.getString(4);
				UserInfo ui = new UserInfo(friend_id, name, email);
				ui.setImage(image);
				list.add(ui);
			}
			
			close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
}
